{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-09T05:47:55.528686Z",
     "start_time": "2019-10-09T05:47:54.412676Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>未知</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>未知</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>未知</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>未知</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>未知</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>未知</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>未知</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>未知</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>未知</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>未知</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>未知</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>未知</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>未知</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>未知</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>未知</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>未知</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>未知</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>未知</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>未知</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>未知</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>未知</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>未知</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>未知</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19457</th>\n",
       "      <td>追飞鸟</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19458</th>\n",
       "      <td>追风鸟</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19459</th>\n",
       "      <td>追风鸟电车</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19460</th>\n",
       "      <td>追风鸟电动车</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19461</th>\n",
       "      <td>追风鸟小飞侠</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19462</th>\n",
       "      <td>追蜂鸟</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19463</th>\n",
       "      <td>追凤鸟</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19464</th>\n",
       "      <td>子母路由器</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19465</th>\n",
       "      <td>子母路由器摄像头</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19466</th>\n",
       "      <td>自动电风扇</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19467</th>\n",
       "      <td>自动拖把</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19468</th>\n",
       "      <td>自动洗衣机</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19469</th>\n",
       "      <td>自行车</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19470</th>\n",
       "      <td>自能电饭锅</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19471</th>\n",
       "      <td>宗申</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19472</th>\n",
       "      <td>宗申电动车</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19473</th>\n",
       "      <td>综合</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19474</th>\n",
       "      <td>租网</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19475</th>\n",
       "      <td>足疗按摩器</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19476</th>\n",
       "      <td>足浴养生机</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19477</th>\n",
       "      <td>组合小度平衡车小电车</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19478</th>\n",
       "      <td>组网</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19479</th>\n",
       "      <td>组网加天翼看家</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19480</th>\n",
       "      <td>组网路由器</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19481</th>\n",
       "      <td>组网设备</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19482</th>\n",
       "      <td>组装电脑</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19483</th>\n",
       "      <td>钻豹</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19484</th>\n",
       "      <td>尊享典藏版</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19485</th>\n",
       "      <td>座机</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19486</th>\n",
       "      <td>座机电话</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19487 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             name  num\n",
       "0              未知   12\n",
       "1              未知    8\n",
       "2              未知    1\n",
       "3              未知    4\n",
       "4              未知    1\n",
       "5              未知   80\n",
       "6              未知   57\n",
       "7              未知    9\n",
       "8              未知    2\n",
       "9              未知    1\n",
       "10             未知    5\n",
       "11             未知    3\n",
       "12             未知    1\n",
       "13             未知    8\n",
       "14             未知   10\n",
       "15             未知    5\n",
       "16             未知   32\n",
       "17             未知    1\n",
       "18             未知   37\n",
       "19             未知  162\n",
       "20             未知  137\n",
       "21             未知    8\n",
       "22             未知  128\n",
       "23             未知    4\n",
       "24             未知    1\n",
       "25             未知    1\n",
       "26             未知    9\n",
       "27             未知    1\n",
       "28             未知    6\n",
       "29             未知    2\n",
       "...           ...  ...\n",
       "19457         追飞鸟    1\n",
       "19458         追风鸟   77\n",
       "19459       追风鸟电车    1\n",
       "19460      追风鸟电动车   43\n",
       "19461      追风鸟小飞侠    1\n",
       "19462         追蜂鸟    3\n",
       "19463         追凤鸟    1\n",
       "19464       子母路由器    4\n",
       "19465    子母路由器摄像头    1\n",
       "19466       自动电风扇    1\n",
       "19467        自动拖把    1\n",
       "19468       自动洗衣机    1\n",
       "19469         自行车   42\n",
       "19470       自能电饭锅    1\n",
       "19471          宗申    3\n",
       "19472       宗申电动车    4\n",
       "19473          综合    1\n",
       "19474          租网    1\n",
       "19475       足疗按摩器    1\n",
       "19476       足浴养生机    1\n",
       "19477  组合小度平衡车小电车    1\n",
       "19478          组网    2\n",
       "19479     组网加天翼看家    1\n",
       "19480       组网路由器    1\n",
       "19481        组网设备    5\n",
       "19482        组装电脑    1\n",
       "19483          钻豹    1\n",
       "19484       尊享典藏版    1\n",
       "19485          座机    1\n",
       "19486        座机电话    1\n",
       "\n",
       "[19487 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "os.chdir(r'D:\\work\\Excel')\n",
    "data= pd.read_excel('商品名称统计.xlsx')\n",
    "data.rename(columns={'COMMODITY_NAME':'name','COUNT(DISTINCTE.CERT_NO_CIPHER':'num'},inplace=True)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-09T05:48:13.334610Z",
     "start_time": "2019-10-09T05:48:02.643126Z"
    }
   },
   "outputs": [],
   "source": [
    "import re\n",
    "def update_name(x):\n",
    "    if re.search('a9',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('a5',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('畅享',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('Y93',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('百度',str(x['name']),re.I):\n",
    "        return '智能音箱'\n",
    "    elif re.search('reno',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('麦芒',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('x27',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('p30',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('手机',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('华为',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('vivo',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('oppo',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('苹果',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('iphone',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('畅玩',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('荣耀',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('r1',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('麦芒',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('三星',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('中兴',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('小辣椒',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('红米',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('酷派',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('note',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('y7',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('y9',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('y8',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    elif re.search('y3',str(x['name']),re.I):\n",
    "        return '手机'\n",
    "    \n",
    "    elif re.search('百度',str(x['name']),re.I):\n",
    "        return '智能音箱'\n",
    "    elif re.search('音箱',str(x['name']),re.I):\n",
    "        return '智能音箱'\n",
    "    elif re.search('小度',str(x['name']),re.I):\n",
    "        return '智能音箱'\n",
    "    elif re.search('音响',str(x['name']),re.I):\n",
    "        return '智能音箱'\n",
    "    elif re.search('天猫',str(x['name']),re.I):\n",
    "        return '智能音箱'\n",
    "    \n",
    "    elif re.search('路由',str(x['name']),re.I):\n",
    "        return '路由器'\n",
    "    \n",
    "    elif re.search('车',str(x['name']),re.I):\n",
    "        return '电动车'\n",
    "    \n",
    "    elif re.search('电视',str(x['name']),re.I):\n",
    "        return '电视'\n",
    "    elif re.search('5寸',str(x['name']),re.I):\n",
    "        return '电视'\n",
    "    elif re.search('0寸',str(x['name']),re.I):\n",
    "        return '电视'\n",
    "    elif re.search('寸',str(x['name']),re.I):\n",
    "        return '电视'\n",
    "    \n",
    "    elif re.search('智',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    elif re.search('能',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    elif re.search('云米',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    elif re.search('萤石',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    elif re.search('手表',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    elif re.search('电话',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    elif re.search('摄像',str(x['name']),re.I):\n",
    "        return '3c'\n",
    "    \n",
    "    elif re.search('洗衣机',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('自动',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('长虹',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('锅',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('新飞',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('炉',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('王牌',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('荣事达',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('美的',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('扇',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('柜',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('空调',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('九阳',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('净水',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('净化',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('海尔',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('格力',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('格兰仕',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('飞科',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('飞利浦',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('创维',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('天然',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('苏泊尔',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('冰箱',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('空调',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('电器',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    elif re.search('机器',str(x['name']),re.I):\n",
    "        return '家电'\n",
    "    \n",
    "\n",
    "    \n",
    "    else:\n",
    "        return x['name']\n",
    "data['name'] = data.apply(lambda x:update_name(x),axis=1)\n",
    "data.to_excel('test1.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
